package com.cloudpivot.receivales.contract.repository.erpcontract;

import com.cloudpivot.receivales.contract.entity.erpcontract.CrmContractPull;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CrmContractPullRepository extends JpaRepository<CrmContractPull,String> {

    @Query(nativeQuery = true,value = "select row_number() over( order by  bc.Id ) rowno , b.strContractID, b.strContractName,b.strContractStartDate,b.strContractEndDate,b.strContractOrderDate,b.dblTotalCurrency,b.strContractDesc,b.cdefine11,dep.cdepname,b.strCurrency,b.dblExchange,cus.ccusname,per.cPersonName,b.strRepair,b.strSetupPerson,b.strInurePerson,cdefine10, b.cdefine13,b.cdefine12,bb.chdefine1,bb.chdefine2,cus.ccusdefine7,bc.strInvoiceID ,bc.strName,bc.dblQuantity,bc.strMeasureUnit,bc.dblTaxRatio,bc.dblPrice,bc.dblSumRMB,bc.dblSum,bc.dtStartDate,bc.dtEndDate, bbc.cbdefine1,bbc.cbdefine14,bbc.cbdefine15,bbc.cbdefine16,bbc.cbdefine17,bbc.cbdefine18,bc.strMemo ,bc.cdefine32  from CM_Contract_B b left join CM_Contract_B_extradefine bb on bb.guid=b.guid left join department dep on b.strDeptID =dep.cdepcode left  join customer cus on cus.ccuscode=b.strBisectionUnit left join person per on per.cpersoncode=b.strPersonID left join CM_Contract_Item_B bc on bc.guid=b.guid left join CM_Contract_Item_B_extradefine bbc on bc.rowguid=bbc.rowguid where b.strContractID=:strContractID")
    List<CrmContractPull> getCrmContractPulls(@Param(value = "strContractID") String strContractID);
}
